﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using Microsoft.Office.Interop.Excel;

namespace ZLDC.Topo.BLL.Estate
{
    public class lp_Share
    {
        public static int InsertSQLServer(System.Data.DataTable dt, string dataname, string userid, string deptid, string deptname, string username)
        {

            string strColumn = string.Empty;
            string strSQL = string.Empty;
            strColumn = " ShareDate,ShareName,ShareArea,ShareRegion,ShareBranch,ShareDepartment,ShareDistrict,ShareCity,ShareCompany,CreateTime,CreateUser,CreateDeptID,CreateDeptName,CreateUserName ";
            for (int i = 0; i < dt.Rows.Count; i++)
            {


                if (dt.Rows[i][0].ToString() != "")
                {
                    //if (bll.Exists(dt.Rows[i][0].ToString()))
                    //{
                    //    Alter = Alter + dt.Rows[i][0].ToString() + ",";

                    //}
                    //else
                    //{
                    strSQL += " Insert into " + dataname + " (" + strColumn + ") values ( ";

                    for (int k = 0; k < dt.Columns.Count; k++)
                    {
                        if (k == 0)
                        {
                            strSQL += "'" + dt.Rows[i][k].ToString() + "-01',";
                        }
                        else
                        {
                            strSQL += "'" + dt.Rows[i][k].ToString() + "',";
                        }

                    }
                    strSQL += " '" + DateTime.Now + "'," + userid + "," + deptid + ",'" + deptname + "','" + username + "'";

                    //strSQL = strSQL.Substring(0, strSQL.Length - 1);
                    strSQL += "); ";


                    //}

                }
            }
            return ZLDC.Topo.BLL.Estate.lp_Basis.Execution_sql(strSQL);

        }

        public static void DownloadFile(string filename, System.Web.UI.Page p)//文件下载函数 
        {

            string saveFileName = "市场占有率.xls";

            int intStart = filename.LastIndexOf("/") + 1;

            saveFileName = filename.Substring(intStart, filename.Length - intStart);

            p.Response.Clear();

            p.Response.Charset = "UTF8";

            p.Response.Buffer = true;

            p.EnableViewState = false;

            // Response.ContentEncoding = System.Text.Encoding.UTF8;

            p.Response.ContentType = "application/ms-excel";
            p.Response.ContentEncoding = System.Text.Encoding.UTF8;
            saveFileName = System.Web.HttpUtility.UrlEncode(saveFileName, System.Text.Encoding.UTF8);
            p.Response.AppendHeader("Content-Disposition", "attachment;filename=" + saveFileName);

            p.Response.TransmitFile(filename);

            p.Response.Flush();

            p.Response.Close();

            p.Response.End();

        }

        public static void GetExcel(System.Web.UI.Page p, System.Web.UI.HtmlControls.HtmlInputText iStartDay, System.Web.UI.HtmlControls.HtmlInputText iEndDay, System.Web.UI.HtmlControls.HtmlInputText txtename, System.Web.UI.HtmlControls.HtmlInputText txtDistrict, System.Web.UI.HtmlControls.HtmlInputText txtDepartment, string deptname)
        {

            string startday = iStartDay.Value;
            string endday = iEndDay.Value;
            string ename = txtename.Value;
            string district = txtDistrict.Value;
            string department = txtDepartment.Value;

            string sql = " select CONVERT(varchar(7),ShareDate,120) as ShareDate,ShareName,ShareArea,ShareRegion,ShareBranch,ShareDepartment,ShareDistrict,ShareCity,ShareCompany from lp_Share where 1=1 ";

            if (startday != "0")
            {
                sql += " and ShareDate>='" + startday + "-01' ";
            }
            if (endday != "0")
            {
                sql += " and ShareDate<='" + endday + "-31' ";
            }
            if (ename != "")
            {
                sql += " and ShareName='" + ename + "' ";
            }
            if (district != "")
            {
                sql += " and sharedistrict='" + district + "' ";
            }
            if (department != "")
            {
                sql += " and department like '" + department + "%' ";
            }


            ExportDsToXls(p, "Share", sql);
        }

        public static void ExportDsToXls(System.Web.UI.Page page, string fileName, string sql)
        {
            System.Data.DataTable dt = ZLDC.Topo.BLL.Estate.lp_Basis.Getds(sql).Tables[0];
            //DataTable dt = SqlHelper.ExecuteReader(sql);
            if (dt != null)
            {
                ZLDC.Topo.BLL.Estate.lp_Share.ExportDsToXls1(page, "" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls", dt);
            }
        }
        /// <summary>
        /// 报名记录
        /// </summary>
        /// <param name="page"></param>
        /// <param name="fileName"></param>
        /// <param name="dt"></param>
        public static void ExportDsToXls1(System.Web.UI.Page page, string fileName, System.Data.DataTable dt)
        {

            string columns = "时间,楼盘名称,片区,行政区,分行,营业部,区域,全市,我司";
            StreamExport(dt, columns.Split(','), fileName, page);
        }
        /// <summary>
        /// DataTable通过流导出Excel
        /// </summary>
        /// <param name="ds">数据源DataSet</param>
        /// <param name="columns">DataTable中列对应的列名(可以是中文),若为null则取DataTable中的字段名</param>
        /// <param name="fileName">保存文件名(例如：a.xls)</param>
        /// <returns></returns>
        public static bool StreamExport(System.Data.DataTable dt, string[] columns, string fileName, System.Web.UI.Page pages)
        {
            if (dt.Rows.Count > 65535) //总行数大于Excel的行数 
            {
                throw new Exception("预导出的数据总行数大于excel的行数");
            }
            if (string.IsNullOrEmpty(fileName)) return false;

            StringBuilder content = new StringBuilder();
            StringBuilder strtitle = new StringBuilder();
            content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
            content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">");
            //注意：[if gte mso 9]到[endif]之间的代码，用于显示Excel的网格线，若不想显示Excel的网格线，可以去掉此代码
            content.Append("<!--[if gte mso 9]>");
            content.Append("<xml>");
            content.Append(" <x:ExcelWorkbook>");
            content.Append("  <x:ExcelWorksheets>");
            content.Append("   <x:ExcelWorksheet>");
            content.Append("    <x:Name>Sheet1</x:Name>");
            content.Append("    <x:WorksheetOptions>");
            content.Append("      <x:Print>");
            content.Append("       <x:ValidPrinterInfo />");
            content.Append("      </x:Print>");
            content.Append("    </x:WorksheetOptions>");
            content.Append("   </x:ExcelWorksheet>");
            content.Append("  </x:ExcelWorksheets>");
            content.Append("</x:ExcelWorkbook>");
            content.Append("</xml>");
            content.Append("<![endif]-->");
            content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>");

            if (columns != null)
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    if (columns[i] != null && columns[i] != "")
                    {
                        content.Append("<td><b>" + columns[i] + "</b></td>");
                    }
                    else
                    {
                        content.Append("<td><b>" + dt.Columns[i].ColumnName + "</b></td>");
                    }
                }
            }
            else
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>");
                }
            }
            content.Append("</tr>\n");

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                content.Append("<tr>");
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    object obj = dt.Rows[j][k];
                    Type type = obj.GetType();
                    if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal")
                    {
                        double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj);
                        if (type.Name == "Int32" || (d - Math.Truncate(d) == 0))
                            content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);
                        else
                            content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00'>{0}</td>", obj);
                    }
                    else
                        content.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj);
                }
                content.Append("</tr>\n");
            }
            content.Append("</table></body></html>");
            content.Replace("&nbsp;", "");
            pages.Response.Clear();
            pages.Response.Buffer = true;
            pages.Response.ContentType = "application/ms-excel";  //"application/ms-excel";
            pages.Response.Charset = "gb2312";
            pages.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.GetEncoding("GB2312"));
            pages.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
            pages.Response.Write(content.ToString());

            //pages.Response.End();  //注意，若使用此代码结束响应可能会出现“由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。”的异常。
            HttpContext.Current.ApplicationInstance.CompleteRequest(); //用此行代码代替上一行代码，则不会出现上面所说的异常。
            return true;
        }
    }
}
